/* Subversion Info: $Id: $ */

/* Drop the current Projects table */
PROMPT Dropping table: PROJECTS
set term off
DROP TABLE project CASCADE constraints;
set term on

/* Drop the current Project PK sequence */
PROMPT Dropping sequence: PROJECT_PK_SEQ
set term off
DROP SEQUENCE project_pk_seq;
set term on

/* Project PK sequence to be used for auto NUMBER PRIMARY KEY sequence */
PROMPT Creating sequence: PROJECT_PK_SEQ
set term off
CREATE SEQUENCE project_pk_seq 
INCREMENT BY 1
START WITH 1
NOCACHE;
set term on

/* Create the Projects table */
PROMPT Creating table: PROJECTS
set term off
CREATE TABLE projects
  (
     id                     NUMBER(2),
     NAME                   VARCHAR2(32) NOT NULL,
     ser                    VARCHAR2(12),
     work_request           VARCHAR2(32),
     msn_summaries_file     VARCHAR2(255),
     cr                     VARCHAR2(24),
     test_report            VARCHAR2(255),
     summary                VARCHAR2(512),
     completed				NUMBER(1) DEFAULT 0,
     archived				NUMBER(1) DEFAULT 0,
     aircraft_id            NUMBER(2),
     separation_engineer_id NUMBER(3),
     project_manager_id     NUMBER(3),
     test_engineer_id       NUMBER(3),
     constraint project_pk primary key (id),
     constraint prj_aircraft_fk foreign key (aircraft_id) references aircraft(id) ON DELETE CASCADE,
     constraint prj_sepeng_fk foreign key (separation_engineer_id) references personnel(id) ON DELETE SET NULL,
     constraint prj_prjmgr_fk foreign key (project_manager_id) references personnel(id) ON DELETE SET NULL,
     constraint prj_testeng_fk foreign key (test_engineer_id) references personnel(id) ON DELETE SET NULL
  );

GRANT SELECT, INSERT, UPDATE, DELETE ON ftdbadm.projects TO ftdbuser;
set term on

/* Projects insert trigger */
PROMPT Creating trigger: PROJECTS_PK_TRIG
set term off
CREATE OR REPLACE TRIGGER project_pk_trig 
  before INSERT ON projects
  FOR each ROW
BEGIN
    SELECT project_pk_seq.nextval
    INTO   :new.id
    FROM   dual;
END;
/
set term on

/* Commit all installation changes */
set term off
COMMIT;
set term on

PROMPT
PROMPT Configuration Complete: PROJECTS
PROMPT